<?php
/* ******************************
 * Pos aJax actions
* ******************************
*/

include('../../includes/classes/core.php');
$action = $_REQUEST['act'];
$error	= '';
$data	= '';

switch ($action) {
	case 'get_list':
		$count		= $_REQUEST['count'];
		$hidden 	= $_REQUEST['hidden'];
		$object_id	= $_REQUEST['obj_id'];
		
		$rResult = mysql_query("
 									SELECT 		`sale_transaction_detail`.`id`,
												`production`.`name`,
												`production`.`code`,
												`sale_transaction_detail`.`cost`,
												`sale_transaction_detail`.`product_quantity`,
												 ROUND(`sale_transaction_detail`.`cost` * `sale_transaction_detail`.`product_quantity`, 2 )
									FROM 		`sale_transaction` 
									LEFT JOIN 	`sale_transaction_detail`
									ON 			`sale_transaction`.`id` = `sale_transaction_detail`.`transaction_id`
									LEFT JOIN 	`production`
									ON 			`sale_transaction_detail`.`production_id` = `production`.`id`
									WHERE   	`sale_transaction`.`object_id` = $object_id && `status` = 1
    	    						");
		
		$data = array(
			"aaData"	=> array()
		);
		
		if(!$rResult){
			$error = 'Invalid query: ' . mysql_error();
		}else{
			while ( $aRow = mysql_fetch_array( $rResult ) )
			{
				$row = array();
				for ( $i = 0 ; $i < $count ; $i++ )
				{
					/* General output */
					$row[] = $aRow[$i];
					if($i == ($count - 1)){
						$row[] = '<a class="pos-prod-delete"/>' . $aRow[$hidden] . '</a>';
					}
				}
				$data['aaData'][] = $row;
			}
		}
		
		break;
	case 'add_prod':
		$prod			= $_REQUEST['name'];
		$object_id 		= $_REQUEST['obj_id'];
		$prod_id 		= GetProductID($prod);
		
		$price_type		= $_REQUEST['price'];
		$price			= GetProductPrice($prod_id, $price_type);
		
		if(!$prod_id){
			$error = 'პროდუქტი ვერ მოიძებნა!';
		}else{
			$status	= CheckObjectStatus($object_id);
			if (!$status) {
				$prod_exist = CheckProdExist($prod_id, 1, $object_id);
				if ($prod_exist) {					
					if(!$price){
						$error = 'დააფიქსირეთ პროდუქტის ფასი!';
					}else{
						$trans_id = AddTransaction($object_id);
						AddProduct($prod_id, $object_id, $price, $trans_id);
					}
				}else{
					$error = 'პროდუქტი ვერ მოიძებნა!';
				}
			} else {
				$quantity = GetProdQuantity($status, $prod_id);
				
				$prod_exist = CheckProdExist($prod_id, $quantity + 1, $object_id);
				if ($prod_exist) {					
					if(!$price){
						$error = 'დააფიქსირეთ პროდუქტის ფასი!';
					}else{
						AddProduct($prod_id, $object_id, $price, $status);
					}
				}else{
					$error = 'არასაკმარისი პროდუქტი!';
				}
			}
		}
		
		break;
    case 'delete_prod':
		$trans_det_id 	= $_REQUEST['trans_id'];
		$object_id 		= $_REQUEST['obj_id'];
		
		DeleteProduct($trans_det_id);
		if(CheckTransStatus($object_id)){
			DeleteTransaction($object_id);
		}
		
        break;
	case 'save_transaction':
		$object_id 		= $_REQUEST['obj_id'];
		
		$status	= CheckObjectStatus($object_id);
				
		if (!$status) {
			$error = 'პროდუქციის სია ცარიელია!';
		}else {
			SaveTransaction($object_id);
		}
		
		break;
	case 'abort_transaction':
		$object_id 		= $_REQUEST['obj_id'];
        
		AbortTransaction($object_id);
		
		break;        	        	
	case 'get_prod_count':
		$object_id 		= $_REQUEST['obj_id'];
		
		$data	= array('prod_count' => GetProdCount($object_id));
		
        break;
	case 'get_sum_price':
		$object_id 		= $_REQUEST['obj_id'];
		
		$data	= array('sum_price' => GetSumPrice($object_id));
		
        break;
    case 'select_pos':
		$data	= array('page' => SelectPOS());
		
        break;
	case 'suspended_page':
		$object_id 		= $_REQUEST['obj_id'];
		$data	= array('page' => GetSuspendedPage($object_id));
		
		break;
	case 'restore_suspended':
		$object_id 		= $_REQUEST['obj_id'];
		$trans_id		= $_REQUEST['trans_id'];
		
		$status	= CheckObjectStatus($object_id);
		
		if (!$status) {
			RestoreSuspended($trans_id);
		}else {
			SaveTransaction($object_id);
			RestoreSuspended($trans_id);
		}		
		
		break;
	case 'cancel_suspend':
		$trans_id		= $_REQUEST['trans_id'];
		
		CancelSuspended($trans_id);
		
		break;
    case 'payment_done':
		$object_id			= $_REQUEST['obj_id'];
		$sum_cost			= GetSumPrice($object_id);
		$sum_self_cost		= GetSumSelfPrice($object_id);
		$calculate			= Calculate($object_id);
		
		if(!$calculate){
            PayDone($object_id, $sum_cost, $sum_self_cost);
        }else{
			$error = $calculate . ' არ არის საწყობში!';
        }
        
        break;
    default:
       $error = 'Action is Null';
}

$data['error'] = $error;

echo json_encode($data);


/* ******************************
 *	Pos Functions
* ******************************
*/

function AddProduct($prod_id, $object_id, $price, $trans_id) 
{
	$self_cost 	= $price[0];
	$cost		= $price[1];
	$cost_type	= $price[2];
	
	$result = mysql_query("	SELECT	`sale_transaction_detail`.`production_id`                                     
					      	FROM	`sale_transaction_detail` LEFT JOIN `production`
					       		ON	`sale_transaction_detail`.`production_id` = `production`.`id` LEFT JOIN `sale_transaction`
						   		ON	`sale_transaction_detail`.`transaction_id` = `sale_transaction`.`id`
					       	WHERE	(`sale_transaction`.`object_id` = '$object_id' && `status` = 1) && (`sale_transaction_detail`.`production_id` = '$prod_id') && (`sale_transaction_detail`.`cost_type` = $cost_type)");
	
	if (mysql_num_rows($result)){
		 mysql_query("	UPDATE
		 					`sale_transaction_detail` LEFT JOIN `sale_transaction`
								ON sale_transaction_detail.transaction_id = sale_transaction.id
						SET
							`sale_transaction_detail`.`product_quantity` =  `sale_transaction_detail`.`product_quantity` + 1
						WHERE
							`sale_transaction_detail`.`production_id` = $prod_id &&  `sale_transaction`.`status` = 1 && transaction_id = $trans_id  && `sale_transaction_detail`.`cost_type` = $cost_type");
	
	}else{
	    mysql_query("INSERT INTO `sale_transaction_detail`
						(`transaction_id`, `production_id`, `product_quantity`, `self_cost`, `cost`, `cost_type` )
					 VALUES
						($trans_id, $prod_id, 1, $self_cost, $cost, $cost_type)");
	}
}

function DeleteProduct($trans_det_id)
{
    mysql_query("DELETE 
				 FROM
	    			`sale_transaction_detail`
				 WHERE
					`id` = $trans_det_id");
}

function DeleteTransaction($object_id)
{
    mysql_query("DELETE 
				 FROM
	    			`sale_transaction`
				 WHERE
					`object_id` = $object_id && `status` = 1");
}

function AddTransaction($object_id)
{
	$start_date = date('Y-m-d H:i:s');
	mysql_query("INSERT INTO `sale_transaction`
					(`object_id`, `start_date`) 
				 VALUES
					($object_id, '$start_date')");
	return mysql_insert_id();	
}

function SaveTransaction($object_id)
{
	mysql_query("
				UPDATE        `sale_transaction_detail`
				LEFT JOIN     `sale_transaction`
					ON 		  `sale_transaction_detail`.`transaction_id` = `sale_transaction`.`id`
				SET           `sale_transaction`.`status` = 2
				WHERE         `sale_transaction`.`object_id` = $object_id   AND  `sale_transaction`.`status` = 1
				");
}

function AbortTransaction($object_id)
{
	mysql_query("
				UPDATE		`sale_transaction_detail`
				LEFT JOIN	`sale_transaction`
					ON		`sale_transaction_detail`.`transaction_id` = `sale_transaction`.`id`
				SET			`sale_transaction`.`status` = 3
				WHERE		`sale_transaction`.`object_id` = $object_id   AND  `sale_transaction`.`status` = 1
	            ");
}

function RestoreSuspended($trans_id)
{
	mysql_query("
					UPDATE		`sale_transaction`
						SET		`sale_transaction`.`status` = 1
					WHERE		`sale_transaction`.`id` = $trans_id
				");
}

function CancelSuspended($trans_id)
{
	mysql_query("
					UPDATE		`sale_transaction`
						SET		`sale_transaction`.`status` = 3
					WHERE		`sale_transaction`.`id` = $trans_id
				");
}

function GetProductID($prod)
{
	$prod = htmlspecialchars($prod, ENT_QUOTES);
	$res = mysql_fetch_assoc(mysql_query("	SELECT `id`
											FROM `production`
											WHERE `code` = '$prod' || `name` = '$prod'"));
	return $res['id'];
}

function CheckObjectStatus($object_id)
{
    $res = mysql_query("SELECT `sale_transaction`.`id`
					    FROM `object` LEFT JOIN `sale_transaction`
							ON `object`.`id` = `sale_transaction`.`object_id`
					    WHERE `object`.`id` = $object_id && `sale_transaction`.`status` = 1");
	if ( mysql_num_rows($res) == 0){
		return false;
	}
	$row = mysql_fetch_assoc($res);
	return $row['id'];
}

function CheckTransStatus($object_id)
{
    $res = mysql_query("SELECT *
						FROM `sale_transaction` RIGHT JOIN `sale_transaction_detail`
							ON `sale_transaction`.`id` = `sale_transaction_detail`.`transaction_id`
						WHERE `object_id` = $object_id && `status` = 1");
	if (mysql_num_rows($res) == 0){
		return true;
	}
	return false;
}

function GetProductPrice($prod_id, $price_type) 
{
	$colum = 'price';
	if($price_type == 1){
		$colum = 'price_wholesale';
	}

    $res = mysql_query("SELECT 		`pricelist`.`$colum` AS `plPrice`, 
    								`reserve`.price AS `pPrice`
					    FROM 		`pricelist` 
    					INNER JOIN 	`production` 
				   		ON 			`pricelist`.`production_id` = `production`.`id`
    					LEFT JOIN   `reserve`
    					ON 			`production`.`id` = `reserve`.`production_id` 
					    WHERE 		`production`.`id` = $prod_id");
    if (mysql_num_rows($res) == 0 ){
		return false;
	}		
	$row = mysql_fetch_assoc($res);
	
	return array($row[pPrice], $row[plPrice], $price_type);
}

function GetProdCount($object_id)
{
    $res = mysql_query("SELECT SUM(`sale_transaction_detail`.`product_quantity`) AS `quantity`
						FROM `sale_transaction` RIGHT JOIN `sale_transaction_detail`
							ON `sale_transaction`.`id` = `sale_transaction_detail`.`transaction_id`
						WHERE `object_id` = $object_id && `status` = 1
						GROUP BY `sale_transaction_detail`.`transaction_id`");
	$row = mysql_fetch_assoc($res);
	return round($row['quantity'], 2);
}

function GetProdQuantity($trans_id, $prod_id)
{
	$res = mysql_query("SELECT	`product_quantity`
						FROM	`sale_transaction_detail`
						WHERE	`transaction_id` = $trans_id && `production_id` = $prod_id");
	$row = mysql_fetch_assoc($res);
	return $row['product_quantity'];
}

function GetSumPrice($object_id)
{
    $res = mysql_query("SELECT ROUND(SUM(`sale_transaction_detail`.`cost` * `sale_transaction_detail`.`product_quantity`), 2 ) AS `cost`
						FROM `sale_transaction` RIGHT JOIN `sale_transaction_detail`
							ON `sale_transaction`.`id` = `sale_transaction_detail`.`transaction_id`
						WHERE `object_id` = $object_id && `status` = 1
						GROUP BY `sale_transaction_detail`.`transaction_id`");
	$row = mysql_fetch_assoc($res);
	return round($row['cost'], 2 );
}

function GetSumSelfPrice($object_id)
{
    $res = mysql_query("SELECT ROUND(SUM(`sale_transaction_detail`.`self_cost` * `sale_transaction_detail`.`product_quantity`), 2 ) AS `self_cost`
						FROM `sale_transaction` RIGHT JOIN `sale_transaction_detail`
							ON `sale_transaction`.`id` = `sale_transaction_detail`.`transaction_id`
						WHERE `object_id` = $object_id && `status` = 1
						GROUP BY `sale_transaction_detail`.`transaction_id`");
	$row = mysql_fetch_assoc($res);
	return round($row['self_cost'], 2 );
}

function SelectPOS()
{
	$data = '		
		<div id="dialog-form">
		    <fieldset>
		    	<table class="dialog-form-table">
					<tr>
						<td style="width: 170px;"><label for="obj_id">აირჩიეთ სალარო</label></td>
						<td>
							<select id="obj_id" class="idls">' . GetObjectList() . '</select>
						</td>
					</tr>
				</table>
	        </fieldset>
	    </div>';
	return $data;
}

function GetObjectList()
{
	$data = '';
    $req = mysql_query("SELECT `id`, `name`
						FROM `object`
						WHERE `type` = 5");
		
	while( $res = mysql_fetch_assoc($req)){			
		$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
	}
		
	return $data;
}

function GetSuspendedPage($object_id)
{
	$data = '
		<div id="dialog-form">
		    <fieldset>
		    	<table class="dialog-form-inner-table">
					<tr>
						<th class="ui-state-default">ტრანზაქცია</th>
						<th class="ui-state-default">თარიღი</th>
						<td class="ui-state-default" style="width: 115px">&nbsp;</td>
						<td class="ui-state-default" style="width: 100px">&nbsp;</td>
					</tr>';
	$req = mysql_query("SELECT `id`, `start_date`
			FROM `sale_transaction`
			WHERE `object_id` = $object_id && `status` = 2");
		
	while( $res = mysql_fetch_assoc($req)){
		$data .= '
					<tr>
						<td>' . $res['id'] . '</td>
						<td>' . $res['start_date'] . '</td>
						<td><button class="restore_suspend_button" trans="' . $res['id'] . '">აღდგენა</button></td>
						<td><button class="cancel_suspend_button" trans="' . $res['id'] . '">წაშლა</button></td>
					</tr>';
	}
	$data .= '
				</table>
	        </fieldset>
	    </div>
        ';
	return $data;
}

function CheckProdExist($prod_id, $quantity, $object_id)
{
	$warehouse = GetWarehouseObject($object_id);
	
	$row = mysql_fetch_assoc(mysql_query("	SELECT 		SUM(`reserve`.`quantity` - `reserve`.`sold`) AS `quantity`
										  	FROM 		`reserve` 
											LEFT JOIN 	`object`
											ON 			`object`.`id` = `reserve`.`object_id`
										  	WHERE 		`object`.`id` = $warehouse && `reserve`.`production_id` = $prod_id
										  	GROUP BY 	`object`.`warehouse`	
										"));
	if($row['quantity'] >= $quantity){
		return true;
	}
	return false;
}

function PayDone($object_id, $sum_cost, $sum_self_cost)
{
	$end_date = date('Y-m-d H:i:s');
	
	mysql_query("UPDATE
						`sale_transaction`
				  SET
						`end_date`			=  '$end_date',
						`sum_self_cost`		=  $sum_self_cost,
						`sum_cost`          =  $sum_cost,
						`status`			=  0
				  WHERE `object_id` = $object_id && `status` = 1");
}

function Calculate($object_id){
	$result = mysql_query("	SELECT 			`production`.`id` AS id,
											`production`.`name` AS name,
											`sale_transaction_detail`.`product_quantity` AS quantity,
											`object`.`warehouse`AS  object
							FROM			`sale_transaction_detail` 
							INNER JOIN		`production`
								ON			`sale_transaction_detail`.`production_id` = `production`.`id`
							INNER JOIN		`sale_transaction`
								ON			`sale_transaction_detail`.`transaction_id` = `sale_transaction`.`id`
							LEFT JOIN		`object` ON `sale_transaction`.`object_id` = `object`.`id`
							WHERE			`sale_transaction`.`object_id` = $object_id  AND `sale_transaction`.`status` = 1");		
	
	$complete	= false;
	$prod_name	= '';
	
	while( $req = mysql_fetch_assoc( $result )){
		if(!CheckProdExist($req['id'], $req['quantity'], $object_id)){
			$complete = true;
			$prod_name = $req['quantity'] . ' ' . $req['name'];
		}
	}
	
	if($complete){
		return $prod_name;
	}
	
	mysql_data_seek($result, 0);
	
	while( $req = mysql_fetch_assoc( $result )){
		SaleDone($req['id'], $req['quantity'], $req['object']);		
	}
	return false;
}

function GetWarehouseObject($object_id){
	$query  = mysql_query("	SELECT 	`warehouse`
							FROM  	`object`
							WHERE  	`id` = $object_id");
	$result = mysql_fetch_assoc($query);
	return $result['warehouse'];
}

function SaleDone($prod_id, $quantity, $object)
{
	$req_quantity = $quantity;
	$res = mysql_query("SELECT  	`reserve`.`id`,
									`reserve`.`production_id`,
									`reserve`.`object_id`,
									`reserve`.`sold`,
									`reserve`.`quantity` - `reserve`.`sold` AS `diff`,
									`reserve`.`create_date`
						FROM 		`reserve`
						WHERE 		`reserve`.`object_id` = $object && `reserve`.`production_id` = $prod_id
						ORDER BY 	`reserve`.`create_date` ASC" );
	
	while ($row = mysql_fetch_array($res)) {
		if ($req_quantity != 0) {
			if($row['diff'] > $req_quantity){
				$sum = $row['sold'] + $req_quantity;
				mysql_query("	UPDATE	`reserve`
								SET		`sold` = $sum
								WHERE	`id` = $row[id]" );
				$req_quantity = 0;
			}else if($row['diff'] == $req_quantity){
				$sum = $row['sold'] + $row['diff'];
				mysql_query("	UPDATE	`reserve`
								SET		`sold` = $sum
								WHERE	`id` = $row[id]" );
				$req_quantity = 0;
			}else{
				$sum = $row['sold'] + $row['diff'];
				mysql_query("	UPDATE	`reserve`
								SET		`sold` = $sum
								WHERE	`id` = $row[id]" );
				$req_quantity = $req_quantity - $row['diff'];
			}
		}
	}
}
?>